package com.conversationboard.database;

/**
 *
 * Potentially long-running queries only to be used by batch processes.
 * 
 * @author Keith Watson
 *
 */

public class BatchQueries {

	public static final String TOP_POSTERS_OF_ALL_TIME = "SELECT displayname, count(*) AS posts FROM messages GROUP BY displayname ORDER BY count(*) DESC LIMIT 100";

	public static final String TOP_POSTERS_THIS_MONTH = "SELECT displayname, count(*) AS posts FROM messages WHERE (timestamp >= ?) AND (timestamp < ?) GROUP BY displayname ORDER BY count(*) DESC LIMIT 100";

	public static final String POSTS_PER_MONTH = "SELECT year(timestamp) AS year, month(timestamp) AS month, count(*) AS posts FROM messages GROUP BY month(timestamp), year(timestamp) ORDER BY year(timestamp), month(timestamp)";

	public static final String POSTS_PER_WEEK = "SELECT year(timestamp) AS year, week(timestamp) AS week, count(*) AS posts FROM messages GROUP BY week(timestamp), year(timestamp) ORDER BY year(timestamp), week(timestamp)";

	public static final String STYLESHEET_USAGE = "SELECT stylesheet, COUNT(*) AS total FROM users GROUP BY stylesheet ORDER BY total DESC";
	
	public static final String THREADS_PER_BOARD = "SELECT boards.name AS name, count(threadid) AS threads FROM boards, threads WHERE (boards.boardid = threads.boardid) AND (NOT boards.access = 'Private') GROUP BY boards.name ORDER BY count(threadid) DESC";

	public static final String THREAD_STARTERS = "SELECT displayname AS name, count(*) AS threads FROM threads GROUP BY displayname ORDER BY count(*) DESC LIMIT 50";

	public static final String MOST_POPULAR_THREADS = "SELECT threads.boardid, threads.threadid, title, count(messages.messageid) as posts FROM threads, messages WHERE (threads.boardid = messages.boardid) AND (threads.threadid = messages.threadid) AND messages.timestamp > ? GROUP BY messages.threadid ORDER BY posts DESC LIMIT 50";

	public static final String GET_POLLS_DUE_TOMORROW = "SELECT * FROM threads WHERE datediff(current_timestamp, pollclosingdate) = -1";

	public static final String GET_POLLS_DUE_TODAY = "SELECT * FROM threads WHERE datediff(current_timestamp, pollclosingdate) = 0";

	public static final String GET_USERS_EXCEEDING_BAN_THRESHOLD = "SELECT banloginid, COUNT(*) AS total FROM banrequests GROUP BY banloginid HAVING total > ?";

	public static final String GET_THREADS_NEEDING_INDEXED = "SELECT * FROM threads WHERE indexed=false";

	public static final String GET_MESSAGES_NEEDING_INDEXED = "SELECT * FROM messages WHERE indexed=false";

	public static final String GET_THREADS_NEEDING_REINDEXED = "SELECT * FROM threads WHERE reindex=true";

	public static final String GET_MESSAGES_NEEDING_REINDEXED = "SELECT * FROM messages WHERE reindex=true";

	public static final String MARK_THREADS_INDEXED = "UPDATE threads SET indexed=true WHERE indexed=false AND timestamp <= ?";

	public static final String MARK_MESSAGES_INDEXED = "UPDATE messages SET indexed=true WHERE indexed=false AND timestamp <= ?";

	public static final String MARK_THREADS_REINDEXED = "UPDATE threads SET reindex=false WHERE reindex=true AND timestamp <= ?";

	public static final String MARK_MESSAGES_REINDEXED = "UPDATE messages SET reindex=false WHERE reindex=true AND timestamp <= ?";

	public static final String REVOKE_TEMPORARY_BANS = "DELETE FROM banned WHERE CURRENT_TIMESTAMP > banneduntil";

	public static final String GET_ALL_THREADS = "SELECT * FROM threads";

	public static final String COUNT_ALL_MESSAGES = "SELECT COUNT(*) AS total FROM messages";

	public static final String COUNT_ALL_THREADS = "SELECT COUNT(*) AS total FROM threads";

	public static final String COUNT_ALL_USERS = "SELECT COUNT(*) AS total FROM users";

	public static final String MAX_MESSAGEID_PER_THREAD = "SELECT boardid, threadid, max(messageid) AS latestmessageid FROM messages GROUP BY boardid, threadid";
	
	public static final String GET_ALL_LOGINIDS = "SELECT loginid FROM users";
	
	public static final String GET_ALL_LOGINIDS_FROM_MESSAGES = "SELECT userid FROM messages";
	
	public static final String UPDATE_USER_MESSAGE_COUNT = "UPDATE users SET postcount=? WHERE loginid=?";

	public static final String DELETE_OLD_SUGGEST_BANS = "DELETE FROM banrequests WHERE timestamp < ?";

	public static final String GET_BAN_REQUESTS = "SELECT banloginid, displayname, originaldisplayname, COUNT(*) AS total FROM banrequests, users WHERE users.loginid=banrequests.banloginid GROUP BY banloginid HAVING total > 4 ORDER BY total DESC";

	public static final String UPDATE_BROKEN_LATEST_MESSAGE_IDS = "UPDATE threads SET latestmessageid=? WHERE boardid=? AND threadid=? AND latestmessageid=0";
}
